/**
 * 
 */
package com.et114.test.oracle2mysql;

import com.et114.components.junit.BaseTestCase;
import com.et114.core.utility.IOTools;

import java.sql.Types;
import java.sql.Connection; 
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;

import java.util.*;

import java.io.FileOutputStream;

/**
 * @author guanhw
 */
public class Oracle2Mysql extends BaseTestCase {

	private String oracleDriver = "oracle.jdbc.driver.OracleDriver"; 
	private String oracleUrl = "jdbc:oracle:thin:@localhost:1521:fw2"; 
	private String oracleUser = "guanhw"; 
	private String oraclePass = "guanhw"; 
	private String oracleschema = "GUANHW" ; 
	
	private String mysqlDriver ; 
	private String mysqlUrl ; 
	private String mysqlUser ; 
	private String mysqlPass ; 
	
	public Oracle2Mysql ( ) {
		// TODO Auto-generated constructor stub
	}
	
	public Connection getOracleConn( ) throws Exception  {
		Class.forName ( oracleDriver ) ; 
		return DriverManager.getConnection ( oracleUrl , oracleUser , oraclePass ) ;
	}
	
	public Connection getMysqlConn( ) throws Exception  {
		Class.forName ( mysqlDriver ) ; 
		return DriverManager.getConnection ( mysqlUrl , mysqlUser , mysqlPass ) ;		
	}
	
	
	public Vector  listOracleTableNames(  ) throws Exception {
		Vector tableNames = new Vector();
	    Connection con = getOracleConn();
	    DatabaseMetaData md = con.getMetaData();

	    String types[] = new String[] { "TABLE", "VIEW" };
	    
	    ResultSet rs;
	    rs = md.getTables( con.getCatalog(), oracleschema , null, types);

	    int count = 0;
	    String name;
	    while (rs.next()) {
	      name = /*rs.getString( 2 ) + "." +*/ rs.getString(3) ; 
	      tableNames.addElement( name );
	      count++;
	    }
	    rs.close();		
	    return tableNames ; 
	}
	
	
	public Map listOracleColumnInfo ( String table , int describeColumn ,
			String schem ) throws Exception {
		Map columnTypes = new HashMap( ) ; 
		int index = table.indexOf ( '.' );
		String schema = null , name;
		DatabaseMetaData md = this.getOracleConn ( ).getMetaData ( );
		String tilde = md.getIdentifierQuoteString ( );
		if ( index > - 1 ) {
			schema = table.substring ( 0 , index );
			if ( schema.startsWith ( tilde ) ) {
				schema = schema.substring ( tilde.length ( ) , schema.length ( )
						- tilde.length ( ) );
			}
			name = table.substring ( index + 1 , table.length ( ) );
			if ( name.startsWith ( tilde ) ) {
				name = name.substring ( tilde.length ( ) , name.length ( )
						- tilde.length ( ) );
			}
		} else {
			name = table;
			if ( name.startsWith ( tilde ) ) {
				name = name.substring ( tilde.length ( ) , name.length ( )
						- tilde.length ( ) );
			}
		}
		
		
		ResultSet rs = md.getColumns ( null , schema , name , "%" );
		while (rs.next ( )) {
			String columnName = rs.getString ( describeColumn );
			int columnType = rs.getInt ( 5 ) ; 
			columnTypes.put ( columnName , new Integer ( columnType ) ) ; 
		}
	
		rs.close ( );
	    return columnTypes;	    
	}
	
	public String  oracle( String table ) throws Exception  {
		return null ;
	}
	
	public String  generateMysqlCommand( String table ) throws Exception  {
		Map cts = listOracleColumnInfo( table , 4 , oracleschema ) ;
		String insertSql = "INSERT INTO " + table + " ( " ; 
		StringBuffer insertSQL = new StringBuffer(  ""  ) ; 
		insertSQL.append ( "DELETE FROM "  ).append ( table ) .append ( ";" ).append ( "\n" ) ;
		StringBuffer cols = null ;
		StringBuffer vals = null ;
		
		String selectSql = "SELECT * FROM " + table + "  " ; 
		java.sql.Connection con = this.getOracleConn ( ) ; 
		java.sql.Statement st = null;
		java.sql.ResultSet rs = null; 
		try {
			st = con.createStatement ( ) ;
			rs = st.executeQuery ( selectSql ) ; 
			while( rs.next ( ) ) {
				vals = new StringBuffer( ) ; 
				cols = new StringBuffer( ) ;
				String colname = null ; 
				int type = 0 ; 
				for(  java.util.Iterator iter = cts.keySet ( ).iterator ( ) ; iter.hasNext ( ) ;  ) {
					colname = iter.next ( ).toString ( ) ; 
					cols.append ( colname ) .append ( "," ) ; 
					type = ( ( Integer ) cts.get ( colname ) ).intValue ( )  ;
					Object colValue = rs.getObject ( colname ) ;
					switch ( type ) {
						case Types.CHAR:
							
							colValue = "'" + ( colValue == null ? "" : colValue ) +  "'"; 
							break ;
						case Types.VARCHAR:
							colValue = "'" + ( colValue == null ? "" : colValue ) +  "'"; 
							break ;
							
						case Types.LONGVARCHAR:
							break ;
						case Types.DATE:
							colValue = "'" + ( colValue == null ? "" : colValue ) +  "'"; 
							break ;
							
						case Types.TIME:
							colValue = "'" + ( colValue == null ? "" : colValue ) +  "'"; 
							break ;
						case Types.TIMESTAMP:
							colValue = "'" + ( colValue == null ? "" : colValue ) +  "'"; 
							break ;

						
						case Types.BIGINT:
							colValue = "" +  colValue  ; 
				
							break ;
						case Types.DECIMAL:
							colValue = "" +  colValue  ; 
							break ;
						case Types.DOUBLE:
							colValue = "" +  colValue  ; 
							break ;
							
						case Types.FLOAT:
							colValue = "" +  colValue  ; 
							break ;
						case Types.INTEGER:
							colValue = "" +  colValue  ; 
							break ;
						case Types.NUMERIC: break ;
						case Types.REAL: break ;
						case Types.SMALLINT: break ;
						case Types.TINYINT: 
							colValue = "" +  colValue ; 
							break ;
						case Types.BIT:
							break ;
						default:
							break ;
					}
					
					vals.append ( (   colValue ) ) .append ( "," ) ; 
					
					
				
				}				
				if ( cols != null  ) {
					insertSQL.append (
							insertSql
									+ ( cols.substring ( 0 ,
											cols.length ( ) - 1 ) ) ).append (
							" ) VALUES ( " ).append (
							( vals.substring ( 0 , vals.length ( ) - 1 ) ) )
							.append ( " ) " );
					insertSQL.append ( ";" ) ; 
					insertSQL.append ( "\n" ) ;
				}
								
			}
			
			insertSQL.append ( "commit;" ) ; 
			insertSQL.append ( "\n" ) ; 
			return insertSQL.toString ( ) ;
		}
		catch ( Exception ex ) {
			ex.printStackTrace ( ) ;
		}
		finally {
			try {
				if ( rs != null ) rs.close ( ) ; 
				if ( st != null ) st.close ( ) ; 
				if ( con != null  )  con.close ( ) ; 
			} catch ( Exception exx ) { exx.printStackTrace ( ) ; }			
		}
		return null ; 
	}
	
	
	/**
	 * @param args
	 */
	public static void main ( String [ ] args ) {
		// TODO Auto-generated method stub
		Oracle2Mysql o2m = new Oracle2Mysql ( ) ;
		StringBuffer sb = new StringBuffer( ) ;
		try {
			Vector v = o2m.listOracleTableNames( ) ;
			for ( int i = 0 ; i < v.size ( ) ; i ++ ) {
				String tn = ( String )v.get ( i ) ;
				String sql = o2m.generateMysqlCommand ( tn ) ;
				sb.append ( sql ) .append ( "\n" ) ; 
				sb.append ( "" ) ;
				FileOutputStream out = new FileOutputStream( "d:/bak/" + tn + ".sql" ) ;
				IOTools.flow ( sql.getBytes ( ) , out ) ; 
			}
			
			FileOutputStream out = new FileOutputStream( "d:/bak/all.sql" ) ;
			IOTools.flow ( sb.toString ( ).getBytes ( ) , out ) ; 
			
	
			/*
			Map cts =  o2m.listOracleColumnInfo ( "AUTH_ACL" , 4 , "GUANHW" ) ;
			for(  java.util.Iterator iter = cts.keySet ( ).iterator ( ) ; iter.hasNext ( ) ;  ) {
				String colname = iter.next ( ).toString ( ) ; 
				System.out.println ( ">>" + colname + ".." + cts.get ( colname ) ) ; 
			}	*/				
		}catch ( Exception ex ) {
			ex.printStackTrace ( );
		}
	}
	
}